package com.tools.gensql.service;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.tools.gensql.enums.DBType;
import org.noear.solon.annotation.Component;

import java.util.Arrays;

@Component
public class PostgresqlDataQuery implements DataQuery {

    public DBType getDatabaseType() {
        return DBType.POSTGRESQL;
    }

    public String getDriver() {
        return "org.postgresql.Driver";
    }

    public String getUrl(String host, String port, String database, String catalog) {
        return StrUtil.format("jdbc:postgresql://{}:{}/{}?currentSchema={}&useSSL=false", host, port, catalog, database);
    }

    public String getQuerySql(String catalog) {
        var sql =  "SELECT \n" +
                "  table_schema AS databaseName,\n" +
                "  \"table_name\" AS tableName,\n" +
                "  obj_description(table_name :: REGCLASS :: OID, 'pg_class') AS tableComment,\n" +
                "  \"column_name\" AS columnName,\n" +
                "  concat(t1.udt_name, \n" +
                "         CASE \n" +
                "           WHEN t1.character_maximum_length IS NULL THEN '' \n" +
                "           ELSE concat('(', t1.character_maximum_length, ')') \n" +
                "         END) AS columnType,\n" +
                "  is_nullable AS isNullAble,\n" +
                "  t2.description AS columnComment\n" +
                "FROM\n" +
                "  information_schema.COLUMNS t1\n" +
                "  LEFT JOIN pg_description t2 ON t1.dtd_identifier = t2.objsubid :: TEXT \n" +
                "  AND t2.objoid = TABLE_NAME :: REGCLASS :: OID \n" +
                "WHERE\n" +
                "  table_schema = ? AND table_catalog = '{}' AND (table_name like ? OR obj_description(table_name :: REGCLASS :: OID, 'pg_class') like ? " +
                "OR column_name like ? OR t2.description like ?)" +
                "ORDER BY \"table_name\" limit 500";
        return StrUtil.format(sql, catalog);
    }

    @Override
    public String getTablesQuerySql(String catalog) {
        var template = """
                SELECT table_name AS tableName, obj_description(table_name :: REGCLASS :: OID, 'pg_class') AS tableComment
                FROM
                  information_schema.tables
                WHERE
                  table_schema = ?
                  AND table_catalog = '{}'
                ORDER BY
                  table_name
                """;
        return StrUtil.format(template, catalog);
    }

    @Override
    public String getQuerySql(int tablesCount, String catalog) {
        var sql = "SELECT \n" +
                "  table_schema AS databaseName,\n" +
                "  \"table_name\" AS tableName,\n" +
                "  \"column_name\" AS columnName,\n" +
                "  concat(t1.udt_name, \n" +
                "         CASE \n" +
                "           WHEN t1.character_maximum_length IS NULL THEN '' \n" +
                "           ELSE concat('(', t1.character_maximum_length, ')') \n" +
                "         END) AS columnType,\n" +
                "  is_nullable AS isNullAble,\n" +
                "  t2.description AS columnComment\n" +
                "FROM\n" +
                "  information_schema.COLUMNS t1\n" +
                "  LEFT JOIN pg_description t2 ON t1.dtd_identifier = t2.objsubid :: TEXT \n" +
                "  AND t2.objoid = table_name :: REGCLASS :: OID \n" +
                "WHERE\n" +
                "  table_schema = ? AND table_name IN ({}) AND table_catalog = '{}' \n" +
                "ORDER BY \"table_name\"";
        var params = new String[tablesCount];
        Arrays.fill(params, "?");
        return StrUtil.format(sql, ArrayUtil.join(params, ","), catalog);
    }
}
